USE Lib
GO

---------------------------------------------------------------------------
-- BEGIN MAINTENANCE WRAPPER
----------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'FN' AND name = 'fn_timestamp') BEGIN
	DROP FUNCTION tsql.fn_timestamp
	PRINT '<<< DROP FUNCTION tsql.fn_timestamp - Completed with SUCCESS >>>'
END

GO
----------------------------
-- END MAINTENANCE WRAPPER
---------------------------------------------------------------------------

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*================================================================================================
Name:			tsql.fn_timestamp
Type:			User defined function 
Owner:					
Description:	returns current timestamp in YYYYMMDDHHNNSSMSS format as BIGINT
Parameters:		@date = the date to format as timestamp, default is GETDATE()
				@precision = specifies the precision of the returned timestamp
				allowed values: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, MILISECOND(DEFAULT) 
Dependens on:  	
Usage:			SELECT Lib.tsql.fn_timestamp(DEFAULT,DEFAULT) 
--------------------------------------------------------------------------------------------------
Project:		http://sqllib.codeplex.com/
Help:			http://sqllib.codeplex.com/wikipage?title=fn_timestamp&referringTitle=Documentation		
Version:		20120829
SrcCtrlApp:		SVN
SrcCtrlPath:	http://sqllib.codeplex.com/SourceControl/list/changesets
Note*:			CTRL + click to follow the links above
--------------------------------------------------------------------------------------------------
History:		08/20/2008 - Adrian E Dudau - Function created
History:		08/29/2012 - Adrian E Dudau - Added @date, @precission params to increase functionality 
================================================================================================*/
CREATE FUNCTION [tsql].fn_timestamp(
	  @date DATETIME = NULL
	, @precision AS NVARCHAR(50) = 'MS'
	)
RETURNS BIGINT 
AS 
BEGIN /*FUNCTION*/
	DECLARE @Timestamp VARCHAR(50) 
	DECLARE @i INT

	SET @precision = 'MS'
	SELECT @i = CASE WHEN @precision IN('Y','YY','YYYY','YEAR') THEN 4
					 WHEN @precision IN('M','MM','MMM','MONTH') THEN 6
					 WHEN @precision IN('D','DD','DDD','DAY') THEN 8
					 WHEN @precision IN('H','HH','HOUR') THEN 10
					 WHEN @precision IN('MI','NN','MINUTE') THEN 12
					 WHEN @precision IN('S','SS','SECOND') THEN 14
					 WHEN @precision IN('MS','MILISECOND') THEN 17
				 ELSE 0
				END

	SET @date = ISNULL(@date,GETDATE());
	
	SET @Timestamp = CAST(YEAR(@Date) AS VARCHAR(4)) 
				   + RIGHT('00' + CAST(MONTH(@Date) AS VARCHAR(2)),2)
				   + RIGHT('00' + CAST(DAY(@Date) AS VARCHAR(2)),2)
				   + RIGHT('00' + CAST(DATEPART(HOUR,@Date) AS VARCHAR(2)),2)
				   + RIGHT('00' + CAST(DATEPART(MINUTE ,@Date) AS VARCHAR(2)),2)
				   + RIGHT('00' + CAST(DATEPART(SECOND,@Date) AS VARCHAR(2)),2)
				   + RIGHT('000' + CAST(DATEPART(MS,@Date) AS VARCHAR(3)),3)

	SET @Timestamp = LEFT(@Timestamp + REPLICATE('0',20),@i)

	RETURN CAST(@Timestamp AS BIGINT) 
END /*FUNCTION*/
GO
---------------------------------------------------------------------------
-- BEGIN MAINTENANCE WRAPPER
-- Verify completion
----------------------------

IF EXISTS(SELECT * FROM sysobjects WHERE type = 'FN' AND name = 'fn_timestamp') BEGIN 
	PRINT '<<< CREATE FUNCTION tsql.fn_timestamp - Completed with SUCCESS >>>'
	GOTO CREATE_SYNONYMS
END

ELSE BEGIN 
	PRINT '<<< CREATE FUNCTION tsql.fn_timestamp - Completed with ERROR >>>'
END


IF  EXISTS (SELECT * FROM sys.synonyms WHERE name = N'fn_date2ts')
DROP SYNONYM [tsql].[fn_date2ts]

CREATE SYNONYM [tsql].[fn_date2ts] FOR [Lib].[tsql].[fn_timestamp]


GO
----------------------------
-- END MAINTENANCE WRAPPER
---------------------------------------------------------------------------







---------------------------------------------------------------------------
-- BEGIN MAINTENANCE WRAPPER
-- Security
----------------------------

--  GRANT SELECT TO PUBLIC will be used at Db level

----------------------------
-- END MAINTENANCE WRAPPER
---------------------------------------------------------------------------

